﻿// -----------------------------------------------------------------------
// <copyright file="RequestFormImpl.cs" company="">
// TODO: Update copyright text.
// </copyright>
// -----------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using StampController.BL.Models;
using StampController.Core;

namespace StampController.BL
{
    /// <summary>
    ///     TODO: Update summary.
    /// </summary>
    public class RequestFormImpl
    {
        public bool SaveRequestForm(RequestFormModel request)
        {
            using (var dbHelper = new DbHelper())
            {
                try
                {
                    dbHelper.TransactionHandler(DbHelper.TransactionType.Open);
                    object[,] parameter;
                    int requestId;
                    if (string.IsNullOrEmpty(request.RequestID))
                    {
                        parameter = new object[,]
                        {
                            {"@DateRequest", request.DateRequest},
                            {"@DateNeeded", request.DateNeeded},
                            {"@UserID", request.UserID},
                            {"@RequestFor", request.RequestFor},
                            {"@CategoryId", request.CategoryId},
                            {"@Purpose", request.Purpose},
                            {"@Remark", request.Remark},
                            {"@SignByID", request.SignByID},
                            {"@RequestType", request.RequestType},
                            {"@RequiredBy", request.RequiredBy},
                            {"@STID", request.STID},
                            {"@StampID", request.StampID},
                            {"@DefineName", request.DefineName},
                            {"@AssignedTo", request.AssignedTo},
                            {"@PagesNo", request.PagesNo},
                            {"@CopiesNo", request.CopiesNo}
                        };
                        requestId = dbHelper.ExecuteScalar<int>(@"
                        DECLARE @RequestId VARCHAR(20), @Seq VARCHAR(5)
                        SELECT @RequestId = MAX(RequestID) FROM RequestForm WHERE YEAR(DateRequest) = " +
                                                                request.DateRequest.Year +
                                                                @" AND MONTH(DateRequest) = " +
                                                                request.DateRequest.Month + @"
                        IF(@RequestId IS NULL) SET @RequestId = 'ALV-" + request.DateRequest.ToString("yyyyMM") +
                                                                @"-00001'
                        ELSE BEGIN SET @Seq = CONVERT(VARCHAR(5), CAST(RIGHT(@RequestId, 5) AS INT) + 1) 
                        SET @RequestId = 'ALV-" + request.DateRequest.ToString("yyyyMM") +
                                                                @"-' + replicate('0', 5 - len(@Seq)) + @Seq END
                        INSERT INTO RequestForm (RequestId, CategoryId, RequestFor, Purpose, Remark, SignByID, RequestType, RequiredBy, STID, StampID, UserID, DateRequest, DateNeeded,DefineName,AssignedTo,Deleted,IsCompleted,PagesNo,CopiesNo)
                        VALUES (@RequestId, @CategoryId, @RequestFor, @Purpose, @Remark, @SignByID, @RequestType, @RequiredBy, @STID, @StampID, @UserID, @DateRequest, @DateNeeded,@DefineName,@AssignedTo,0,0,@PagesNo,@CopiesNo)
                        SELECT MAX(ID) FROM RequestForm", CommandType.Text,
                            parameter);
                    }
                    else
                    {
                        parameter = new object[,]
                        {
                            {"@RequestID", request.RequestID},
                            {"@DateNeeded", request.DateNeeded},
                            {"@Purpose", request.Purpose},
                            {"@RequestFor", request.RequestFor},
                            {"@CategoryId", request.CategoryId},
                            {"@Remark", request.Remark},
                            {"@SignByID", request.SignByID},
                            {"@RequestType", request.RequestType},
                            {"@RequiredBy", request.RequiredBy},
                            {"@STID", request.STID},
                            {"@StampID", request.StampID},
                            {"@DefineName", request.DefineName},
                            {"@AssignedTo", request.AssignedTo},
                            {"@PagesNo", request.PagesNo},
                            {"@CopiesNo", request.CopiesNo}
                        };
                        requestId = dbHelper.ExecuteScalar<int>(@"
                        UPDATE RequestForm 
                        SET CategoryId = @CategoryId,
                        RequestFor = @RequestFor,
                        Purpose = @Purpose, 
                        Remark = @Remark, 
                        SignByID = @SignByID, 
                        RequestType = @RequestType, 
                        RequiredBy = @RequiredBy, 
                        STID = @STID, 
                        StampID = @StampID, 
                        DateNeeded = @DateNeeded,
                        DefineName = @DefineName,
                        AssignedTo = @AssignedTo,
                        PagesNo = @PagesNo,
                        CopiesNo = @CopiesNo
                        WHERE RequestID = @RequestID
                        SELECT ID FROM RequestForm WHERE RequestID = @RequestID", CommandType.Text,
                            parameter);
                    }

                    request.ID = requestId;
                    foreach (DocumentModel documentModel in request.DocumentList)
                    {
                        parameter = new object[,]
                        {
                            {"@RequestID", requestId},
                            {"@Link", documentModel.Link.StartsWith("/Upload") ? documentModel.Link.Remove(0, "/Upload".Length) : documentModel.Link},
                            {"@OriginName", documentModel.OriginName}
                        };
                        dbHelper.ExecuteNonQuery(@"
                        IF (EXISTS(SELECT * FROM Document WHERE Link = @Link AND RequestID = @RequestID))
                        UPDATE Document 
                        SET OriginName = @OriginName
                        WHERE Link = @Link AND RequestID = @RequestID
                        ELSE
                        INSERT INTO Document (Link, OriginName, RequestID)
                        VALUES (@Link, @OriginName, @RequestID)", CommandType.Text,
                            parameter);
                    }

                    dbHelper.TransactionHandler(DbHelper.TransactionType.Commit);
                }
                catch
                {
                    dbHelper.TransactionHandler(DbHelper.TransactionType.Rollback);
                    throw;
                }
                return true;
            }
        }

        public bool RejectRequestForm(string requestId, ApprovalHistoryModel history)
        {
            using (var dbHelper = new DbHelper())
            {
                try
                {
                    dbHelper.TransactionHandler(DbHelper.TransactionType.Open);
                    object[,] parameter =
                        {
                            {"@RequestId", requestId},
                            {"@ApprovedBy", history.ApprovedBy},
                            {"@ApprovedDate", history.ApprovedDate}
                        };
                    var fieldSet = new StringBuilder("DECLARE @ID INT, @DeptID INT ")
                        .Append("SELECT @ID = ID FROM RequestForm WHERE RequestId = @RequestId ")
                        //.Append("SELECT TOP 1 @DeptID = DeptID FROM ApprovalHistory WHERE RequestID = @ID AND Status = 1 ORDER BY ApprovedDate DESC ")
                        //.Append("IF (@DeptID IS NULL) BEGIN SET @ApprovedBy = NULL SET @ApprovedDate = NULL END ")
                        .Append("UPDATE RequestForm SET ")
                        .Append("LastApprovedBy = @ApprovedBy, LastApprovedDate = @ApprovedDate, AssignedTo = NULL ")
                        .Append("WHERE ID = @ID AND IsCompleted = 0 AND AssignedTo IS NOT NULL");
                    int i = dbHelper.ExecuteNonQuery(fieldSet.ToString(), CommandType.Text, parameter);

                    if (i > 0)
                    {
                        parameter = new object[,]
                        {
                            {"@ID", Guid.NewGuid()},
                            {"@RequestID", requestId},
                            {"@ApprovedBy", history.ApprovedBy},
                            {"@ApprovedDate", DateTime.Now},
                            {"@DeptID", history.DeptID},
                            {"@Comment", history.Comment},
                            {"@AuthorizedBy", history.AuthorizedBy},
                            {"@Status", history.Status}
                        };
                        fieldSet = new StringBuilder(
                            "INSERT INTO ApprovalHistory (ID, RequestID, ApprovedBy, ApprovedDate, DeptID, Comment, Status, AuthorizedBy) ")
                            .Append("VALUES (@ID, (SELECT TOP 1 ID FROM RequestForm WHERE RequestID = @RequestID), @ApprovedBy, @ApprovedDate, @DeptID, @Comment, @Status, @AuthorizedBy)");
                        dbHelper.ExecuteNonQuery(fieldSet.ToString(), CommandType.Text, parameter);
                    }

                    dbHelper.TransactionHandler(DbHelper.TransactionType.Commit);
                }
                catch
                {
                    dbHelper.TransactionHandler(DbHelper.TransactionType.Rollback);
                    return false;
                }
                return true;
            }
        }

        public bool ApproveRequestForm(string requestId, ApprovalHistoryModel history, int? assignedTo = null)
        {
            using (var dbHelper = new DbHelper())
            {
                try
                {
                    bool isCompleted = assignedTo == null;
                    dbHelper.TransactionHandler(DbHelper.TransactionType.Open);
                    object[,] parameter =
                        {
                            {"@RequestId", requestId},
                            {"@ApprovedBy", history.ApprovedBy},
                            {"@ApprovedDate", history.ApprovedDate},
                            {"@AssignedTo", assignedTo},
                            {"@IsCompleted", isCompleted}
                        };
                    var fieldSet = new StringBuilder("UPDATE RequestForm SET ")
                        .Append("LastApprovedBy = @ApprovedBy, LastApprovedDate = @ApprovedDate, AssignedTo = @AssignedTo, IsCompleted = @IsCompleted ")
                        .Append("WHERE RequestId = @RequestId AND IsCompleted = 0");
                    int i = dbHelper.ExecuteNonQuery(fieldSet.ToString(), CommandType.Text, parameter);

                    if (i > 0)
                    {
                        parameter = new object[,]
                        {
                            {"@ID", Guid.NewGuid()},
                            {"@RequestID", requestId},
                            {"@ApprovedBy", history.ApprovedBy},
                            {"@ApprovedDate", history.ApprovedDate},
                            {"@AuthorizedBy",history.AuthorizedBy},
                            {"@DeptID", history.DeptID},
                            {"@Comment", history.Comment},
                            {"@Status", history.Status}
                        };
                        fieldSet = new StringBuilder(
                            "INSERT INTO ApprovalHistory (ID, RequestID, ApprovedBy, ApprovedDate, DeptID, Comment, Status,AuthorizedBy) ")
                            .Append("VALUES (@ID, (SELECT ID FROM RequestForm WHERE RequestID = @RequestID), @ApprovedBy, @ApprovedDate, @DeptID, @Comment, @Status,@AuthorizedBy)");
                        dbHelper.ExecuteNonQuery(fieldSet.ToString(), CommandType.Text, parameter);
                    }

                    dbHelper.TransactionHandler(DbHelper.TransactionType.Commit);
                }
                catch
                {
                    dbHelper.TransactionHandler(DbHelper.TransactionType.Rollback);
                    return false;
                }
                return true;
            }
        }

        public bool AssignRequestForm(string requestId, ApprovalHistoryModel history)
        {
            using (var dbHelper = new DbHelper())
            {
                try
                {
                    dbHelper.TransactionHandler(DbHelper.TransactionType.Open);
                    object[,] parameter =
                        {
                            {"@RequestId", requestId},
                            {"@AssignedTo", history.DeptID}
                        };
                    var fieldSet = new StringBuilder("UPDATE RequestForm SET ")
                        .Append("AssignedTo = @AssignedTo ")
                        .Append("WHERE RequestId = @RequestId AND IsCompleted = 0");
                    int i = dbHelper.ExecuteNonQuery(fieldSet.ToString(), CommandType.Text, parameter);

                    if (i > 0)
                    {
                        parameter = new object[,]
                        {
                            {"@ID", Guid.NewGuid()},
                            {"@RequestID", requestId},
                            {"@ApprovedBy", history.ApprovedBy},
                            {"@ApprovedDate", DateTime.Now},
                            {"@AuthorizedBy",history.AuthorizedBy},
                            {"@DeptID", history.DeptID},
                            {"@Comment", history.Comment},
                            {"@Status", history.Status}
                        };
                        fieldSet = new StringBuilder(
                            "INSERT INTO ApprovalHistory (ID, RequestID, ApprovedBy, ApprovedDate, DeptID, Comment, Status, AuthorizedBy) ")
                            .Append("VALUES (@ID, (SELECT ID FROM RequestForm WHERE RequestID = @RequestID), @ApprovedBy, @ApprovedDate, @DeptID, @Comment, 3, @AuthorizedBy)");
                        dbHelper.ExecuteNonQuery(fieldSet.ToString(), CommandType.Text, parameter);
                    }

                    dbHelper.TransactionHandler(DbHelper.TransactionType.Commit);
                }
                catch
                {
                    dbHelper.TransactionHandler(DbHelper.TransactionType.Rollback);
                    return false;
                }
                return true;
            }
        }

        /// <summary>
        /// This function use for the closing request feature of admin. Base on the stamp kind and site.
        /// </summary>
        /// <param name="sites"></param>
        /// <param name="status"></param>
        /// <param name="keyword"></param>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public IList<RequestFormModel> GetRequestFormList(
            List<int> sites, string status, string keyword,
            DateTime? fromDate, DateTime? toDate,
            int? pageNumber, int? pageSize, out int count)
        {
            try
            {
                using (var dbHelper = new DbHelper())
                {
                    var parameters = new List<DbHelper.Parameters>
                    {
                        new DbHelper.Parameters("@Keyword", '%' + keyword + '%'),
                        new DbHelper.Parameters("@IsClosed", status != "pending")
                    };
                    int i = 0;
                    string inSites = string.Empty;
                    foreach (var dept in sites)
                    {
                        parameters.Add(new DbHelper.Parameters("@Site" + i, dept));
                        inSites += ",@Site" + (i++);
                    }
                    inSites = inSites.Substring(1);

                    using (DataTable dt = dbHelper.GetDataTable(string.Format(
                        @"SELECT A.*, B.Name AS CreatedBy, C.Name AS RequestForName, D.Name AS DeptName, E.CategoryName
                        FROM RequestForm A 
                        LEFT JOIN Users B ON A.UserID = B.UserID
                        LEFT JOIN Users C ON A.RequestFor = C.UserID
                        LEFT JOIN Department D ON A.AssignedTo = D.DeptID
                        LEFT JOIN Category E ON A.CategoryId = E.CategoryId
                        WHERE Purpose LIKE @Keyword AND Deleted = 0 AND IsCompleted = 1 AND IsClosed = @IsClosed AND A.StampID IN ({0})
                        ORDER BY DateRequest DESC", inSites), CommandType.Text, parameters))
                    {
                        var approvalHistoryImpl = new ApprovalHistoryImpl();
                        var list = dt.Select().Select(row => new RequestFormModel
                        {
                            ID = row.Cells<int>("ID"),
                            RequestID = row.Cells<string>("RequestID"),
                            DateRequest = row.Cells<DateTime>("DateRequest"),
                            Purpose = row.Cells<string>("Purpose"),
                            Remark = row.Cells<string>("Remark"),
                            UserID = row.Cells<string>("UserID"),
                            CreatedBy = row.Cells<string>("CreatedBy"),
                            RequestFor = row.Cells<string>("RequestFor"),
                            RequestForName = row.Cells<string>("RequestForName"),
                            SignByID = row.Cells<int>("SignByID"),
                            RequestType = row.Cells<int>("RequestType"),
                            RequiredBy = row.Cells<int>("RequiredBy"),
                            STID = row.Cells<int>("STID"),
                            StampID = row.Cells<int>("StampID"),
                            DeptName = row.Cells<string>("DeptName"),
                            CateName = row.Cells<string>("CategoryName"),
                            LastApprovedDate = row.Cells<DateTime?>("LastApprovedDate"),
                            IsClosed = row.Cells<bool>("IsClosed"),
                            PagesNo = row.Cells<int?>("PagesNo"),
                            CopiesNo = row.Cells<int?>("CopiesNo"),
                            ApprovalHistoryList = approvalHistoryImpl.GetApprovalHistoryList(row.Cells<int>("ID"))
                        }).ToList();

                        count = list.Count();
                        if (pageNumber.HasValue && pageSize.HasValue)
                            return list.Page(pageNumber.Value, pageSize.Value);
                        return list;
                    }
                }
            }
            catch
            {
                count = 0;
                return new List<RequestFormModel>();
            }
        }

        public IList<RequestFormModel> GetRequestFormList(
            string userRequest, string keyword, DateTime? fromDate,
            DateTime? toDate,
            int? pageNumber, int? pageSize, out int count)
        {
            try
            {
                using (var dbHelper = new DbHelper())
                {
                    object[,] parameter =
                    {
                        {"@Keyword", '%' + keyword + '%'},
                        {"@UserRequest", userRequest}
                    };

                    using (DataTable dt = dbHelper.GetDataTable(
                        @"SELECT A.*, B.Name AS CreatedBy, C.Name AS RequestForName, D.Name AS DeptName, E.CategoryName
                        FROM RequestForm A 
                        LEFT JOIN Users B ON A.UserID = B.UserID
                        LEFT JOIN Users C ON A.RequestFor = C.UserID
                        LEFT JOIN Department D ON A.AssignedTo = D.DeptID
                        LEFT JOIN Category E ON A.CategoryId = E.CategoryId
                        WHERE Purpose LIKE @Keyword AND Deleted = 0 AND (A.UserID = @UserRequest OR A.RequestFor = @UserRequest)
                        ORDER BY DateRequest DESC", parameter))
                    {
                        var approvalHistoryImpl = new ApprovalHistoryImpl();
                        var list = dt.Select().Select(row => new RequestFormModel
                        {
                            ID = row.Cells<int>("ID"),
                            RequestID = row.Cells<string>("RequestID"),
                            DateRequest = row.Cells<DateTime>("DateRequest"),
                            Purpose = row.Cells<string>("Purpose"),
                            Remark = row.Cells<string>("Remark"),
                            UserID = row.Cells<string>("UserID"),
                            CreatedBy = row.Cells<string>("CreatedBy"),
                            RequestFor = row.Cells<string>("RequestFor"),
                            RequestForName = row.Cells<string>("RequestForName"),
                            SignByID = row.Cells<int>("SignByID"),
                            RequestType = row.Cells<int>("RequestType"),
                            RequiredBy = row.Cells<int>("RequiredBy"),
                            STID = row.Cells<int>("STID"),
                            StampID = row.Cells<int>("StampID"),
                            DeptName = row.Cells<string>("DeptName"),
                            CateName = row.Cells<string>("CategoryName"),
                            LastApprovedDate = row.Cells<DateTime?>("LastApprovedDate"),
                            PagesNo = row.Cells<int?>("PagesNo"),
                            CopiesNo = row.Cells<int?>("CopiesNo"),
                            ApprovalHistoryList = approvalHistoryImpl.GetApprovalHistoryList(row.Cells<int>("ID"))
                        }).ToList();

                        count = list.Count();
                        if (pageNumber.HasValue && pageSize.HasValue)
                            return list.Page(pageNumber.Value, pageSize.Value);
                        return list;
                    }
                }
            }
            catch
            {
                count = 0;
                return new List<RequestFormModel>();
            }
        }

        public IList<RequestFormModel> GetRequestFormList(
            string approvedBy, List<int> depts, string status, string keyword, DateTime? fromDate,
            DateTime? toDate,
            int? pageNumber, int? pageSize, out int count, out int countPending, out int countApproval,
            out int countDeleted)
        {
            try
            {
                using (var dbHelper = new DbHelper())
                {
                    var parameters = new List<DbHelper.Parameters>
                    {
                        new DbHelper.Parameters("@Keyword", '%' + keyword + '%'),
                        new DbHelper.Parameters("@ApprovedBy", approvedBy)
                    };
                    int i = 0;
                    string inDepts = string.Empty;
                    foreach (var dept in depts)
                    {
                        parameters.Add(new DbHelper.Parameters("@Dept" + i, dept));
                        inDepts += ",@Dept" + (i++);
                    }
                    inDepts = inDepts.Substring(1);

                    string condition;
                    switch (status.ToLower())
                    {
                        case "pending":
                            condition = string.Format(" AND IsCompleted = 0 AND Deleted = 0 AND AssignedTo IN ({0}) ", inDepts);
                            break;
                        case "approved":
                            condition = " AND Deleted = 0 AND ID IN (SELECT RequestId FROM ApprovalHistory WHERE (ApprovedBy = @ApprovedBy OR AuthorizedBy = @ApprovedBy) AND Status = 1) ";
                            break;
                        case "deleted":
                            condition = " AND Deleted = 0 AND ID IN (SELECT RequestId FROM ApprovalHistory WHERE (ApprovedBy = @ApprovedBy OR AuthorizedBy = @ApprovedBy) AND Status = 2) ";
                            break;
                        default:
                            condition = string.Empty;
                            break;
                    }

                    using (DataSet ds = dbHelper.GetDataSet(
                        string.Format(
                        @"SELECT A.*, B.Name AS CreatedBy, C.Name AS RequestForName, D.Name AS DeptName, E.CategoryName
                        INTO #Temp
                        FROM RequestForm A 
                        LEFT JOIN Users B ON A.UserID = B.UserID
                        LEFT JOIN Users C ON A.RequestFor = C.UserID
                        LEFT JOIN Department D ON A.AssignedTo = D.DeptID
                        LEFT JOIN Category E ON A.CategoryId = E.CategoryId
                        WHERE Purpose LIKE @Keyword {0}
                        ORDER BY DateRequest DESC
                        
                        SELECT COUNT(*) FROM #Temp WHERE IsCompleted = 0 AND Deleted = 0 AND AssignedTo IN ({1})
                        SELECT COUNT(*) FROM #Temp WHERE Deleted = 0 AND ID IN (SELECT RequestId FROM ApprovalHistory WHERE (ApprovedBy = @ApprovedBy OR AuthorizedBy = @ApprovedBy) AND Status = 1)
                        SELECT COUNT(*) FROM #Temp WHERE Deleted = 0 AND ID IN (SELECT RequestId FROM ApprovalHistory WHERE (ApprovedBy = @ApprovedBy OR AuthorizedBy = @ApprovedBy) AND Status = 2)
                        SELECT * FROM #Temp
                        DROP TABLE #Temp", condition, inDepts), CommandType.Text, parameters))
                    {
                        countPending = (int)ds.Tables[0].Rows[0][0];
                        countApproval = (int)ds.Tables[1].Rows[0][0];
                        countDeleted = (int)ds.Tables[2].Rows[0][0];

                        var approvalHistoryImpl = new ApprovalHistoryImpl();
                        var list = new List<RequestFormModel>();

                        list.AddRange(
                            ds.Tables[3].Select().Select(row => new RequestFormModel
                            {
                                ID = row.Cells<int>("ID"),
                                RequestID = row.Cells<string>("RequestID"),
                                DateRequest = row.Cells<DateTime>("DateRequest"),
                                Purpose = row.Cells<string>("Purpose"),
                                Remark = row.Cells<string>("Remark"),
                                UserID = row.Cells<string>("UserID"),
                                CreatedBy = row.Cells<string>("CreatedBy"),
                                RequestFor = row.Cells<string>("RequestFor"),
                                RequestForName = row.Cells<string>("RequestForName"),
                                SignByID = row.Cells<int>("SignByID"),
                                RequestType = row.Cells<int>("RequestType"),
                                RequiredBy = row.Cells<int>("RequiredBy"),
                                STID = row.Cells<int>("STID"),
                                StampID = row.Cells<int>("StampID"),
                                DeptName = row.Cells<string>("DeptName"),
                                CateName = row.Cells<string>("CategoryName"),
                                LastApprovedDate = row.Cells<DateTime?>("LastApprovedDate"),
                                PagesNo = row.Cells<int?>("PagesNo"),
                                CopiesNo = row.Cells<int?>("CopiesNo"),
                                ApprovalHistoryList = approvalHistoryImpl.GetApprovalHistoryList(row.Cells<int>("ID"))
                            }));

                        count = list.Count();
                        if (pageNumber.HasValue && pageSize.HasValue)
                            return
                                list.Page(pageNumber.Value, pageSize.Value);

                        return list;
                    }
                }
            }
            catch
            {
                count = countPending = countApproval = countDeleted = 0;
                return new List<RequestFormModel>();
            }
        }

        public RequestFormModel GetRequestForm(string requestId)
        {
            RequestFormModel requestForm = null;
            using (var dbHelper = new DbHelper())
            {
                object[,] parameter =
                {
                    {"@RequestId", requestId}
                };
                using (DbDataReader reader =
                    dbHelper.ExecuteReader(
                        "SELECT A.[ID],[RequestID],A.[UserID],[RequestFor],A.[CategoryId]," +
                        "[DateRequest],[DateNeeded],[Purpose],[SignByID],[RequestType]," +
                        "[RequiredBy],[StampID],[STID],A.[Remark],[LastApprovedBy]," +
                        "[LastApprovedDate],DefineName,AssignedTo,B.Name AS CreatedBy,C.Name AS RequestForName," +
                        "F.Name AS ApprovedName,ISNULL(P.Name, D.Name) AS DeptName,E.CategoryName,A.PagesNo,A.CopiesNo " + // Dept name of request for or user request
                        "FROM RequestForm A " +
                        "LEFT JOIN Users B ON A.UserID = B.UserID " +
                        "LEFT JOIN Users C ON A.RequestFor = C.UserID " +
                        "LEFT JOIN Department D ON B.DeptID = D.DeptID " +
                        "LEFT JOIN Department P ON C.DeptID = P.DeptID " +
                        "LEFT JOIN Category E ON A.CategoryId = E.CategoryId " +
                        "LEFT JOIN Users F ON A.LastApprovedBy = F.UserID " +
                        "WHERE RequestID = @RequestId",
                        parameter))
                {
                    if (reader.HasRows && reader.Read())
                    {
                        requestForm = new RequestFormModel
                        {
                            ID = reader.Get<int>(0),
                            RequestID = reader.Get<string>(1),
                            UserID = reader.Get<string>(2),
                            RequestFor = reader.Get<string>(3),
                            CategoryId = reader.Get<int>(4),
                            DateRequest = reader.Get<DateTime>(5),
                            DateNeeded = reader.Get<DateTime?>(6),
                            Purpose = reader.Get<string>(7),
                            SignByID = reader.Get<int>(8),
                            RequestType = reader.Get<int>(9),
                            RequiredBy = reader.Get<int>(10),
                            StampID = reader.Get<int>(11),
                            STID = reader.Get<int>(12),
                            Remark = reader.GetEmpty(13),
                            LastApprovedBy = reader.Get<string>(14),
                            LastApprovedDate = reader.Get<DateTime?>(15),
                            DefineName = reader.Get<string>(16),
                            AssignedTo = reader.Get<int?>(17),
                            CreatedBy = reader.Get<string>(18),
                            RequestForName = reader.Get<string>(19),
                            ApprovedName = reader.Get<string>(20),
                            DeptName = reader.Get<string>(21),
                            CateName = reader.Get<string>(22),
                            PagesNo = reader.Get<int?>(23),
                            CopiesNo = reader.Get<int?>(24)
                        };

                        reader.Close();

                        parameter = new object[,]
                        {
                            {"@RequestId", requestForm.ID}
                        };
                        using (DbDataReader reader1 =
                            dbHelper.ExecuteReader(
                                "SELECT Link, OriginName FROM Document WHERE RequestID = @RequestId",
                                parameter))
                        {
                            if (reader1.HasRows)
                            {
                                requestForm.DocumentList = new List<DocumentModel>();
                                string uploadFolder = ConfigurationManager.AppSettings["UploadFolder"];
                                while (reader1.Read())
                                {
                                    requestForm.DocumentList.Add(new DocumentModel
                                    {
                                        Link = uploadFolder + reader1.Get<string>(0),
                                        OriginName = reader1.Get<string>(1)
                                    });
                                }
                            }

                            reader1.Close();
                        }
                    }
                }


            }
            return requestForm;
        }

        public RequestFormModel GetRequestForm(int id)
        {
            RequestFormModel requestForm = null;
            using (var dbHelper = new DbHelper())
            {
                object[,] parameter =
                {
                    {"@Id", id}
                };
                using (DbDataReader reader =
                    dbHelper.ExecuteReader(
                        "SELECT A.[ID],[RequestID],A.[UserID],[RequestFor],A.[CategoryId]," +
                        "[DateRequest],[DateNeeded],[Purpose],[SignByID],[RequestType]," +
                        "[RequiredBy],[StampID],[STID],A.[Remark],[LastApprovedBy]," +
                        "[LastApprovedDate],DefineName,AssignedTo,B.Name AS CreatedBy,C.Name AS RequestForName," +
                        "F.Name AS ApprovedName,ISNULL(P.Name, D.Name) AS DeptName,E.CategoryName,A.PagesNo,A.CopiesNo " + // Dept name of request for or user request
                        "FROM RequestForm A " +
                        "LEFT JOIN Users B ON A.UserID = B.UserID " +
                        "LEFT JOIN Users C ON A.RequestFor = C.UserID " +
                        "LEFT JOIN Department D ON B.DeptID = D.DeptID " +
                        "LEFT JOIN Department P ON C.DeptID = P.DeptID " +
                        "LEFT JOIN Category E ON A.CategoryId = E.CategoryId " +
                        "LEFT JOIN Users F ON A.LastApprovedBy = F.UserID " +
                        "WHERE A.ID = @Id",
                        parameter))
                {
                    if (reader.HasRows && reader.Read())
                    {
                        requestForm = new RequestFormModel
                        {
                            ID = reader.Get<int>(0),
                            RequestID = reader.Get<string>(1),
                            UserID = reader.Get<string>(2),
                            RequestFor = reader.Get<string>(3),
                            CategoryId = reader.Get<int>(4),
                            DateRequest = reader.Get<DateTime>(5),
                            DateNeeded = reader.Get<DateTime?>(6),
                            Purpose = reader.Get<string>(7),
                            SignByID = reader.Get<int>(8),
                            RequestType = reader.Get<int>(9),
                            RequiredBy = reader.Get<int>(10),
                            StampID = reader.Get<int>(11),
                            STID = reader.Get<int>(12),
                            Remark = reader.GetEmpty(13),
                            LastApprovedBy = reader.Get<string>(14),
                            LastApprovedDate = reader.Get<DateTime?>(15),
                            DefineName = reader.Get<string>(16),
                            AssignedTo = reader.Get<int?>(17),
                            CreatedBy = reader.Get<string>(18),
                            RequestForName = reader.Get<string>(19),
                            ApprovedName = reader.Get<string>(20),
                            DeptName = reader.Get<string>(21),
                            CateName = reader.Get<string>(22),
                            PagesNo = reader.Get<int?>(23),
                            CopiesNo = reader.Get<int?>(24)
                        };

                        reader.Close();

                        parameter = new object[,]
                        {
                            {"@RequestId", requestForm.ID}
                        };
                        using (DbDataReader reader1 =
                            dbHelper.ExecuteReader(
                                "SELECT Link, OriginName FROM Document WHERE RequestID = @RequestId",
                                parameter))
                        {
                            if (reader1.HasRows)
                            {
                                requestForm.DocumentList = new List<DocumentModel>();
                                string uploadFolder = ConfigurationManager.AppSettings["UploadFolder"];
                                while (reader1.Read())
                                {
                                    requestForm.DocumentList.Add(new DocumentModel
                                    {
                                        Link = uploadFolder + reader1.Get<string>(0),
                                        OriginName = reader1.Get<string>(1)
                                    });
                                }
                            }

                            reader1.Close();
                        }
                    }
                }


            }
            return requestForm;
        }

        public bool DeleteRequestForm(int id)
        {
            using (var dbHelper = new DbHelper())
            {
                object[,] parameter =
                {
                    {"@ID", id}
                };
                int i = dbHelper.ExecuteNonQuery("DELETE FROM RequestForm WHERE ID = @ID AND LastApprovedDate IS NULL", CommandType.Text, parameter);

                return i > 0;
            }
        }

        public bool CloseRequestForms(int[] ids)
        {
            using (var dbHelper = new DbHelper())
            {
                var parameter = new List<DbHelper.Parameters>();
                int k = 0;
                string collect = string.Empty;
                foreach (var id in ids)
                {
                    parameter.Add(new DbHelper.Parameters { ParamName = "@ID" + k, ParamValue = id });
                    collect += ",@ID" + (k++);
                }
                string query = string.Format("UPDATE RequestForm SET IsClosed = 1 WHERE IsClosed = 0 AND IsCompleted = 1 AND Deleted = 0 AND ID IN ({0})",
                    collect.Substring(1));
                int i = dbHelper.ExecuteNonQuery(query, CommandType.Text, parameter);

                return i > 0;
            }
        }

        public bool CloseRequestForm(int id)
        {
            using (var dbHelper = new DbHelper())
            {
                object[,] parameter =
                {
                    {"@ID", id}
                };
                const string query = "UPDATE RequestForm SET IsClosed = 1 WHERE IsClosed = 0 AND IsCompleted = 1 AND Deleted = 0 AND ID = @ID";
                int i = dbHelper.ExecuteNonQuery(query, CommandType.Text, parameter);

                return i > 0;
            }
        }

        public bool CloseRequestForm(string requestId, string userName, DateTime date)
        {
            using (var dbHelper = new DbHelper())
            {
                object[,] parameter =
                {
                    {"@RequestID", requestId},
                    {"@ClosedBy", userName},
                    {"@ClosedDate", date}
                };
                const string query = "UPDATE RequestForm SET IsClosed = 1, ClosedBy = @ClosedBy, ClosedDate = @ClosedDate WHERE IsClosed = 0 AND IsCompleted = 1 AND Deleted = 0 AND RequestID = @RequestID";
                int i = dbHelper.ExecuteNonQuery(query, CommandType.Text, parameter);

                return i > 0;
            }
        }

    }
}